# -*- coding: utf-8 -*-
"""
Created on Thu Aug 25 12:25:53 2022

@author: xiong
"""

# -*- coding: utf-8 -*-
"""
Created on Wed Jul  6 14:46:39 2022

@author: xiong
"""
import pymysql
import logging
logger = logging.getLogger("facebook_spider")


from OutputTemplate.sqlPostsTemplate import sqlPostsTemplate
from OutputTemplate.sqlSharesTemplate import sqlSharesTemplate
from OutputTemplate.sqlLikesTemplate import sqlLikesTemplate
from OutputTemplate.sqlReplysTemplate import sqlReplysTemplate


class SqlExecuter():
    error = ""
    def __init__(self,taskid,reader):
        self.taskid = taskid
        conf = reader.settings
        host,user,password,db,port,charset = conf["sql"]["host"],conf["sql"]["user"],conf["sql"]["password"],conf["sql"]["db"],conf["sql"]["port"],conf["sql"]["charset"],
        try:
            self.db = pymysql.connect(host=host, user=user, password=password, db=db, port= port,charset=charset)
        except Exception as e:
            self.error = e
            
    def add_data(self,data,data_type):
        # 连接数据库
        logger.info("write data to sql ,data_type = " + data_type)
        conn = self.db.cursor()  # 获取光标
        # 注意这里使用的是executemany而不是execute，executemany可以一次插入多条数据，这里插入五条
        # conn.executemany('insert into concept_plate(plate_name,latest_price,change_amount,price_limit,total_value,'
        #                  'turnover_rate,ADVANCE,DECLINE,Led_by_stock,applies) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
        #                  usersvalues) 
        if data_type == "posts":
            sql = sqlPostsTemplate(data,self.taskid)
            conn.execute(sql)
            self.db.commit()
        elif data_type == "share":
            for i in data:
                sql = sqlSharesTemplate(i,self.taskid,i["post_id"])
                conn.execute(sql)
                self.db.commit()
        elif data_type == "like":
            for i in data:
                sql = sqlLikesTemplate(i,self.taskid)
                conn.execute(sql)
                self.db.commit()
        elif data_type == "reply":
            for i in data:
                sql = sqlReplysTemplate(i,self.taskid,i["post_id"])
                conn.execute(sql)
                self.db.commit()
        
        conn.close()
        
        

        def close(self):
            self.db.close()
        print('OK')
    
    
    def update_data():
        pass
        
    def execute_sql(self,sql_command,return_value = False):
        conn = self.db.cursor()  # 获取光标
        conn.execute(sql_command)
        
        if return_value:
            c = list(conn.fetchall())
            conn.close()
            return c
        conn.close()